﻿CREATE PROCEDURE [dbo].[usp_select_Active_AlertSubscriptions]
AS

SET NOCOUNT ON;

DECLARE @CurrentUTCHour tinyint
		,@CurrentUTCMinute tinyint
		,@CurrentUTCDatetime datetime
		,@CurrentUTCTime char(12)

SET @CurrentUTCDatetime = GETUTCDate();

-- 00:00:00:000 format
SET @CurrentUTCTime = CONVERT(varchar(16), @CurrentUTCDatetime, 14);
SET @CurrentUTCHour = CONVERT(tinyint, SUBSTRING(@CurrentUTCTime, 1, 2));
SET @CurrentUTCMinute = CONVERT(tinyint, SUBSTRING(@CurrentUTCTime, 4, 2));

SELECT [AlertSubscriptionID]
      ,[AlertID]
      ,[Active]
      ,[Subscriber]
      ,[IsGroup]
      ,[CustomEmail]
      ,[LastFired]
	  ,[IntervalMinutes]
  FROM [dbo].[AlertSubscription]
  WHERE Active = 1
	AND ((BlackOutStartDate > @CurrentUTCDatetime) OR (BlackOutEndDate < @CurrentUTCDatetime))
	AND 
	(
		(UseStartAndEndTime = 0)
		OR
		(
			(
				--Start time is before end time.  Select the subscriptions where the the current time is between the start time and end time.
				((StartUTCHour < EndUTCHour) OR ((StartUTCHour = EndUTCHour) AND (StartUTCMinute <= EndUTCMinute)))
				AND ((StartUTCHour < @CurrentUTCHour) OR ((StartUTCHour = @CurrentUTCHour) AND (StartUTCMinute <= @CurrentUTCMinute)))
				AND ((EndUTCHour > @CurrentUTCHour) OR ((EndUTCHour = @CurrentUTCHour) AND (EndUTCMinute >= @CurrentUTCMinute)))
			)
			OR
			(
				--Start time is after end time.  This means that the start time or end time crossed over into a different day
				--when it was converted to UTC.  In this case, select the subscriptions where the current time is NOT between the start time and end time.
				((StartUTCHour > EndUTCHour) OR ((StartUTCHour = EndUTCHour) AND (StartUTCMinute >= EndUTCMinute)))
				AND NOT
				(
					((StartUTCHour > @CurrentUTCHour) OR ((StartUTCHour = @CurrentUTCHour) AND (StartUTCMinute >= @CurrentUTCMinute)))
					AND ((EndUTCHour < @CurrentUTCHour) OR ((EndUTCHour = @CurrentUTCHour) AND (EndUTCMinute <= @CurrentUTCMinute)))
				)
			)
		)	
	)	
	
	
	

SET NOCOUNT OFF;
